from typing import List, Optional, Dict, Any
from .db import get_db, get_db_connection
import sqlite3

# 考勤记录相关操作
def get_all_attendance_records() -> List[Dict[str, Any]]:
    """获取所有考勤记录，并关联员工姓名"""
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT ar.*, e.name as employee_name 
            FROM attendance_records ar
            LEFT JOIN employees e ON ar.employee_id = e.employee_id
        """)
        columns = [description[0] for description in cursor.description]
        return [dict(zip(columns, row)) for row in cursor.fetchall()]


def get_attendance_record(record_id: int) -> Optional[Dict[str, Any]]:
    """获取指定考勤记录，并关联员工姓名"""
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT ar.*, e.name as employee_name 
            FROM attendance_records ar
            LEFT JOIN employees e ON ar.employee_id = e.employee_id
            WHERE ar.record_id = ?
        """, (record_id,))
        row = cursor.fetchone()
        if row:
            columns = [description[0] for description in cursor.description]
            return dict(zip(columns, row))
        return None


def create_attendance_record(record_data: Dict[str, Any]) -> Dict[str, Any]:
    """创建新考勤记录"""
    with get_db() as conn:
        cursor = conn.cursor()

        # 检查是否已存在同一工号同一日期的记录
        cursor.execute("""
            SELECT COUNT(*) FROM attendance_records 
            WHERE employee_id = ? AND date = ?
        """, (record_data["employee_id"], record_data["date"]))
        count = cursor.fetchone()[0]
        if count > 0:
            # 记录已存在，抛出异常
            raise sqlite3.IntegrityError(
                f"已存在工号为 {record_data['employee_id']} 且日期为 {record_data['date']} 的考勤记录")

        cursor.execute("""
            INSERT INTO attendance_records (
                employee_id, date, weekday, morning_on_duty, noon_off_duty,
                afternoon_off_duty, night_off_duty, noon_work_hours,
                afternoon_work_hours, day_total_work_hours, night_work_hours,
                overtime_hours, attendance_type_id, overtime_type
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            record_data["employee_id"],
            record_data["date"],
            record_data["weekday"],
            record_data["morning_on_duty"],
            record_data["noon_off_duty"],
            record_data["afternoon_off_duty"],
            record_data["night_off_duty"],
            record_data["noon_work_hours"],
            record_data["afternoon_work_hours"],
            record_data["day_total_work_hours"],
            record_data["night_work_hours"],
            record_data["overtime_hours"],
            record_data["attendance_type_id"],
            record_data["overtime_type"]
        ))
        conn.commit()
        return get_attendance_record(cursor.lastrowid)


def update_attendance_record(record_id: int, record_data: Dict[str, Any]) -> Optional[Dict[str, Any]]:
    """更新考勤记录"""
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            UPDATE attendance_records SET
                employee_id = ?, date = ?, weekday = ?, morning_on_duty = ?,
                noon_off_duty = ?, afternoon_off_duty = ?, night_off_duty = ?,
                noon_work_hours = ?, afternoon_work_hours = ?,
                day_total_work_hours = ?, night_work_hours = ?,
                overtime_hours = ?, attendance_type_id = ?, overtime_type = ?
            WHERE record_id = ?
        """, (
            record_data["employee_id"],
            record_data["date"],
            record_data["weekday"],
            record_data["morning_on_duty"],
            record_data["noon_off_duty"],
            record_data["afternoon_off_duty"],
            record_data["night_off_duty"],
            record_data["noon_work_hours"],
            record_data["afternoon_work_hours"],
            record_data["day_total_work_hours"],
            record_data["night_work_hours"],
            record_data["overtime_hours"],
            record_data["attendance_type_id"],
            record_data["overtime_type"],
            record_id
        ))
        conn.commit()
        return get_attendance_record(record_id)


def delete_attendance_record(record_id: int) -> bool:
    """删除考勤记录"""
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM attendance_records WHERE record_id = ?", (record_id,))
        conn.commit()
        return cursor.rowcount > 0


def batch_create_attendance_records(records: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
    """批量创建考勤记录，添加唯一性检查"""
    created_records = []
    skipped_records = []
    with get_db() as conn:
        cursor = conn.cursor()
        try:
            # 开始事务
            cursor.execute("BEGIN TRANSACTION")

            # 检查每条记录是否已存在（同一工号同一日期）
            for record in records:
                cursor.execute("""
                    SELECT COUNT(*) FROM attendance_records 
                    WHERE employee_id = ? AND date = ?
                """, (record["employee_id"], record["date"]))
                count = cursor.fetchone()[0]
                if count > 0:
                    # 记录已存在，跳过
                    skipped_records.append({
                        "employee_id": record["employee_id"],
                        "date": record["date"]
                    })
                    continue

                # 记录不存在，插入数据
                cursor.execute("""
                    INSERT INTO attendance_records (
                        employee_id, date, weekday, morning_on_duty, noon_off_duty,
                        afternoon_off_duty, night_off_duty, noon_work_hours,
                        afternoon_work_hours, day_total_work_hours, night_work_hours,
                        overtime_hours, attendance_type_id, overtime_type
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """, (
                    record["employee_id"],
                    record["date"],
                    record["weekday"],
                    record["morning_on_duty"],
                    record["noon_off_duty"],
                    record["afternoon_off_duty"],
                    record["night_off_duty"],
                    record["noon_work_hours"],
                    record["afternoon_work_hours"],
                    record["day_total_work_hours"],
                    record["night_work_hours"],
                    record["overtime_hours"],
                    record["attendance_type_id"],
                    record["overtime_type"]
                ))

            # 获取新创建的记录
            if cursor.lastrowid:
                # 获取最后插入的ID
                last_id = cursor.lastrowid

                # 获取新创建的记录，包括员工姓名
                cursor.execute("""
                    SELECT ar.*, e.name as employee_name 
                    FROM attendance_records ar
                    LEFT JOIN employees e ON ar.employee_id = e.employee_id
                    WHERE ar.record_id > ?
                    ORDER BY ar.record_id
                """, (last_id - len(records) + len(skipped_records),))

                # 将结果转换为字典列表
                columns = [description[0] for description in cursor.description]
                created_records = [dict(zip(columns, row)) for row in cursor.fetchall()]

            # 提交事务
            conn.commit()

            # 如果有跳过的记录，添加到日志
            if skipped_records:
                print(f"跳过 {len(skipped_records)} 条重复记录: {skipped_records}")

        except sqlite3.IntegrityError as e:
            # 回滚事务
            conn.rollback()
            print(f"数据完整性错误: {str(e)}")
            raise e
        except Exception as e:
            # 回滚事务
            conn.rollback()
            print(f"意外错误: {str(e)}")
            raise e

    return created_records


def batch_update_attendance_records(record_ids, update_data):
    """批量更新考勤记录"""
    conn = get_db_connection()
    cursor = conn.cursor()

    # 构建更新语句
    set_clause = ", ".join([f"{key} = ?" for key in update_data.keys()])
    placeholders = ",".join(["?"] * len(record_ids))

    # 准备参数
    params = list(update_data.values()) + record_ids

    # 执行更新
    try:
        cursor.execute(f"""
            UPDATE attendance_records
            SET {set_clause}
            WHERE record_id IN ({placeholders})
        """, params)

        updated_count = cursor.rowcount
        conn.commit()
        return updated_count
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()


def batch_delete_attendance_records(record_ids):
    """批量删除考勤记录"""
    conn = get_db_connection()
    cursor = conn.cursor()

    # 构建删除语句
    placeholders = ",".join(["?"] * len(record_ids))

    # 执行删除
    try:
        cursor.execute(f"""
            DELETE FROM attendance_records
            WHERE record_id IN ({placeholders})
        """, record_ids)

        deleted_count = cursor.rowcount
        conn.commit()
        return deleted_count
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()
